/*

EXEC sp_load_assembly_release_lots 1771, 30, -1, 'prod'
select * from Vendors

*/
 
ALTER  PROCEDURE sp_load_assembly_release_lots 
(
	@assembly_process_step_id INT, 
	@from_vendor_id INT, 
	@assembly_release_id INT,
	@release_type VARCHAR(50)
)
AS
BEGIN
	--DECLARE @assembly_process_step_id INT
 	DECLARE @sort_process_step_id INT
 	DECLARE @location_id INT
 	DECLARE @lot_id INT
 	DECLARE @lot_number VARCHAR(50)
	DECLARE @fab_lot_number VARCHAR(50)
 	DECLARE @bin_number VARCHAR(10)
 	DECLARE @quantity INT
	DECLARE @die_bank_qty_in INT
	DECLARE @waferids_count INT
 	DECLARE @goodBinsStr VARCHAR(50)
	DECLARE @lotDiscrepancy BIT
 
 	DECLARE @availableBinsStrForLot VARCHAR(50)
 	DECLARE @tempBinsStrForLot VARCHAR(50)
 	DECLARE @bin VARCHAR(5)
 	DECLARE @columnsString VARCHAR(50)
 	DECLARE @availableQtyStrForLot VARCHAR(100)
 	DECLARE @index INT
 	DECLARE @binBalanceSumQty VARCHAR(20)
 
 	DECLARE @queryString NVARCHAR(1000)
 	DECLARE @ParamDefinition NVARCHAR(500)
 
 	DECLARE  @lot_cursor CURSOR
 	DECLARE  @bin_cursor CURSOR
 	DECLARE  @sort_process_step_cursor CURSOR 	--For Tinitity there are Two "SORT" Processes.
 
 	DECLARE @total_no_of_sort_releases_generated_for_lot INT	--Total No. of Sort Releases excluding Cancelled Releases
 	DEClARE @total_no_of_sort_releases_closed_for_lot INT		--Total No. of Sort Releases in Closed Status
 
 	DECLARE @results TABLE ( Lot_Id INT, Lot_Number VARCHAR(50), Good_Bins VARCHAR(50), Qty_Available VARCHAR(100), Total_Qty_Available_For_This_Lot INT, Timestamp DATETIME, Wafers_Count INT, Fab_Lot_Number VARCHAR(50), Lot_Discrepancy BIT, Bin_Update INT, Vendor_QTY INT )
 	DECLARE @goodBinResultTable Table ( Bin_Number VARCHAR(50) )
	DECLARE @timestamp DATETIME    --For Lot Table Time stamp

	DECLARE @BIN_UPDATE INT

	--SELECT @assembly_process_step_id = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @fg_process_step_id, 0 ) WHERE PS_TYPE = 'Assembly'
	 
 	--Getting the Sort Process Setup Id.
 	--SET @sort_process_step_cursor = CURSOR FOR SELECT ProcessStep_ID FROM PROCESS_STEP WHERE ProcessStep_ID IN ( SELECT  ProcessStep_ID FROM fn_get_process_flow_by_processstep_id ( @assembly_process_step_id )) AND Ps_Type = 'SORT'
	SET @sort_process_step_cursor = CURSOR FOR SELECT ProcessStep_ID FROM PROCESS_STEP WHERE ProcessStep_ID IN ( SELECT  ProcessStep_ID FROM fn_get_process_flow_by_processstep_id ( @assembly_process_step_id )) AND Ps_Type = 'Die Bank'
 	OPEN @sort_process_step_cursor
 	FETCH NEXT FROM @sort_process_step_cursor INTO @sort_process_step_id
 	WHILE @@FETCh_STATUS = 0
 	BEGIN
 		--Getting Location Id of Vendor at Sort Stage.
 		SELECT @location_id = location_id FROM LOCATION WHERE VENDOR_ID = @from_vendor_id AND L_Type = 'Die Bank' 
 
 		DELETE FROM @goodBinResultTable
 		--Getting the Good Bins for the Sort an Assembly Location Combination.
 		INSERT INTO @goodBinResultTable SELECT Bin_Number FROM Assembly_Bin_Mapping WHERE ASSM_PROCESSSTEP_ID = @assembly_process_step_id AND BIN_ProcessStep_ID = @sort_process_step_id

		IF( ( SELECT COUNT(*) FROM @goodBinResultTable ) = 0 )
		BEGIN
 			INSERT INTO @goodBinResultTable values ('Bin1')
		END

 		SET @goodBinsStr = ''
 	
 		SET @bin_cursor = CURSOR FOR SELECT Bin_Number FROM @goodBinResultTable
 		OPEN @bin_cursor
 		FETCH NEXT FROM @bin_cursor INTO @bin_number
 		WHILE @@FETCh_STATUS = 0
 		BEGIN
 			SET @goodBinsStr = @goodBinsStr + @bin_number + '&'
 			FETCH NEXT FROM @bin_cursor INTO @bin_number	
 	
 		END	
 		CLOSE @bin_cursor 
 		DEALLOCATE @bin_cursor	
 	
		--PRINT ' @goodBinsStr = ' + @goodBinsStr

 		IF ( LEN( @goodBinsStr ) > 0 )
 		BEGIN
 			SET @goodBinsStr = SUBSTRING( @goodBinsStr, 0, LEN(@goodBinsStr) )
 		END

		--PRINT ' @sort_process_step_id = ' + convert( varchar, @sort_process_step_id ) + ' @location_id = ' + convert( varchar, @location_id )
 	
 		SET @lot_cursor = CURSOR FOR SELECT DISTINCT L.LOT_ID, LOT_NUMBER, TIMESTAMP, Fab_Lot_Number FROM LOT_TABLE L, SORT_BIN_RELEASE_TABLE S WHERE L.LOT_ID = S.LOT_ID AND L.PROCESSSTEP_ID = @sort_process_step_id AND LOCATION_ID = @location_id AND L.Lot_Current_Status = 'Running'
 		OPEN @lot_cursor
 		FETCH NEXT FROM @lot_cursor INTO @lot_id, @lot_Number, @timestamp, @fab_lot_number
 		WHILE @@FETCh_STATUS = 0
 		BEGIN
 			
			SELECT @waferids_count = COUNT(*) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id AND Bin_1_Balance_Qty > 0

 			SELECT @total_no_of_sort_releases_generated_for_lot = Count(*) FROM SORT_RELEASE_LOT_TABLE SRLT, SORT_RELEASE_TABLE SRT
 			       WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND SRT.STATUS <> 'Cancelled' AND LOT_NUMBER = @lot_Number
 
 			SELECT @total_no_of_sort_releases_closed_for_lot = Count(*) FROM SORT_RELEASE_LOT_TABLE SRLT, SORT_RELEASE_TABLE SRT
 			       WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND LOT_NUMBER = @lot_Number
 				AND  SRT.STATUS = 'CLOSED' 

 			IF ( @total_no_of_sort_releases_generated_for_lot IS NULL ) 
 			BEGIN
 				SET @total_no_of_sort_releases_generated_for_lot = 0
 			END
 
 			IF ( @total_no_of_sort_releases_closed_for_lot IS NULL ) 
 			BEGIN
 				SET @total_no_of_sort_releases_closed_for_lot = 0
 			END
		
			--PRINT ' @total_no_of_sort_releases_generated_for_lot = ' + convert( varchar, @total_no_of_sort_releases_generated_for_lot ) + ' @total_no_of_sort_releases_closed_for_lot = ' + convert( varchar, @total_no_of_sort_releases_closed_for_lot )
 
 			--Checking Whether Sort Release is Closed for the Lot. 
 			IF ( @total_no_of_sort_releases_generated_for_lot = @total_no_of_sort_releases_closed_for_lot )
 			BEGIN
 
 				IF @lot_id <> -1 AND @lot_id <> 0
 				BEGIN
 					SET @quantity = 0
 					SET @availableBinsStrForLot = ''
 	
 					SET @bin_cursor = CURSOR FOR SELECT Bin_Number FROM @goodBinResultTable ORDER BY Bin_Number	
 					OPEN @bin_cursor
 					FETCH NEXT FROM @bin_cursor INTO @bin_number
 					WHILE @@FETCh_STATUS = 0
 					BEGIN
		 				--PRINT '-------------- @bin_number = ' + @bin_number					
 						IF @bin_number = 'Bin1' 
 						BEGIN
 		
 							IF ( ( SELECT SUM( ISNULL( BIN_1_BALANCE_QTY, 0 ) ) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_1_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin1' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin2' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_2_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_2_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin2' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin3' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_3_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_3_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin3' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin4' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_4_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_4_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin4' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin5' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_5_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_5_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin5' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin6' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_6_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_6_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin6' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin7' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_7_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_7_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin7' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin8' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_8_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_8_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin8' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin9' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_9_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_9_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin9' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin10' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_10_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_10_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin10' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin11' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_11_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_11_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin11' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin12' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_12_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_12_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin12' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin13' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_13_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_13_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin13' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin14' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_14_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_14_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot + 'Bin14' + '&'
 							END
 						END
 						ELSE IF @bin_number = 'Bin15' 
 						BEGIN
 		
 							IF ( ( SELECT SUM(ISNULL( BIN_15_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id ) > 0 ) 
 							BEGIN
 								SET @quantity = @quantity + ( SELECT SUM(ISNULL( BIN_15_BALANCE_QTY, 0 )) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @lot_id )
 								SET @availableBinsStrForLot = @availableBinsStrForLot  + 'Bin15' + '&'
 							END
 						END

						--PRINT '-------------- @availableBinsStrForLot = ' + @availableBinsStrForLot
 		
 						FETCH NEXT FROM @bin_cursor INTO @bin_number	
 		
 					END	
 					CLOSE @bin_cursor 
 					DEALLOCATE @bin_cursor	
 				
 				END		
 		
 				SET @availableQtyStrForLot = ''		
 		
 				IF ( @quantity > 0 )
 				BEGIN
 					IF ( LEN( @availableBinsStrForLot ) > 0 )
 					BEGIN
 						--For removing extra '&', which is at the end.
 						SET @tempBinsStrForLot  = @availableBinsStrForLot
 						SET @availableBinsStrForLot = SUBSTRING( @availableBinsStrForLot, 0, LEN(@availableBinsStrForLot) )
 					END
 			
 					--For getting the Qty for each selected Bin and forming it with '&' delimeter
 					WHILE ( LEN(@tempBinsStrForLot) > 0 )
 					BEGIN
 						IF ( CHARINDEX ( '&', @tempBinsStrForLot, 1 ) > 0 ) 
 						BEGIN
 							SET @index = CHARINDEX ( '&', @tempBinsStrForLot, 1 )
 							SET @bin = substring( @tempBinsStrForLot, 0, @index ) 
 							SET @columnsString = 'SUM ( ' + REPLACE(@bin, 'Bin','Bin_')+'_BALANCE_QTY' + ' ) '
 							SET @tempBinsStrForLot = SUBSTRING( @tempBinsStrForLot, @index+1, LEN( @tempBinsStrForLot ) )
 		
 							SET @queryString = N'declare tempCursor cursor for SELECT '+@columnsString+' from SORT_BIN_RELEASE_TABLE WHERE Lot_Id = @lot_id' 
 							set @ParamDefinition = N'@lot_id int'
 							execute sp_executesql @queryString, @ParamDefinition, @lot_id = @lot_id
 							OPEN tempCursor
 							FETCH NEXT FROM tempCursor INTO @binBalanceSumQty
 							SET @availableQtyStrForLot = @availableQtyStrForLot + @binBalanceSumQty + '&'
 					
 							CLOSE tempCursor
 							DEALLOCATE tempCursor 
 		
 						END	
 					END
 		
 					--For removing extra '&', which is at the end.
 					IF ( LEN( @availableQtyStrForLot ) > 0 )
 					BEGIN
 						SET @availableQtyStrForLot = SUBSTRING( @availableQtyStrForLot, 0, LEN(@availableQtyStrForLot) )
 					END
 					
 					SET @lotDiscrepancy = 0
					SELECT @die_bank_qty_in = Qty_In, @BIN_UPDATE = ISNULL(SHOP_ORDER_NUMBER,2) FROM LOT_TABLE WHERE LOT_ID = @lot_id
					IF ( ISNULL( @die_bank_qty_in, 0) != @quantity )
					BEGIN
						SET @lotDiscrepancy = 1
					END
					 		
 					INSERT INTO @results ( Lot_Id, Lot_Number, Good_Bins, Qty_Available, Total_Qty_Available_For_This_Lot, Timestamp, Wafers_Count, Fab_Lot_Number, Lot_Discrepancy, Bin_Update,Vendor_QTY ) 
								VALUES ( @lot_id, @lot_Number, @availableBinsStrForLot, @availableQtyStrForLot, @quantity, @timestamp, @waferids_count, @fab_lot_number, @lotDiscrepancy,@BIN_UPDATE, @die_bank_qty_in )
 				END	
 			END

			SET @waferids_count = 0

 			FETCH NEXT FROM @lot_cursor INTO @lot_id, @lot_Number, @timestamp, @fab_lot_number
 		END
 		CLOSE @lot_cursor 
 		DEALLOCATE @lot_cursor
 
 		FETCH NEXT FROM @sort_process_step_cursor INTO @sort_process_step_id
 	END
 
 	CLOSE @sort_process_step_cursor 
 	DEALLOCATE @sort_process_step_cursor
 
 
	SELECT * FROM @RESULTS  ORDER BY Timestamp 
END
